#!/usr/bin/evn python
# -*- encoding: utf-8 -*-

import os
import sys
import pytz
import xlrd
import xlwt
import time
import json
import MySQLdb
import datetime
import xlutils.copy
import urllib, urllib2
import redis


class Attendance:
    SQL = "SELECT userId, username FROM  test.user "
    # SQL = "SELECT userId, username FROM  communion.user "

    def __init__(self, host='', username='', password='', redis='', charset='utf8'):
        self.host = host
        self.username = username
        self.password = password
        self.charset = charset
        self.redisConf = redis

        self.dbconn = None
        self.cursor = None

        self.db = None
        self.redisdbconn = None

        self.users = {}
        self.attendances = {}
        self.checkins = {}

        self.notSignedKey = u"notSignedCount_attendance_Records"
        self.datas = {}  # 连续三天未打卡
        self.datasAll = {}  # 全部考勤

        self.delusers = {}  # 删除日期重整数据结构

    def openDBConnect(self):
        self.dbconn = MySQLdb.connect(self.host, self.username, self.password, charset=self.charset)

    def getDBCursor(self):

        if not self.dbconn:
            self.openDBConnect()

        self.cursor = self.dbconn.cursor()

    def closeDBConnect(self):
        if self.dbconn:
            self.dbconn.close()
            self.dbconn = None

    # redis连接池
    def redisPool(self):
        r = redis.ConnectionPool(host=self.redisConf.get("host"), password=self.redisConf.get("password"), db=self.db, decode_responses=True)
        self.redisdbconn = redis.Redis(connection_pool=r)

    def post(self, url, args, local=True):
        if local:
            req = urllib2.Request(url=url, data=urllib.urlencode(args))  # urllib.urlencode(args) / json.dumps(args)
            return json.loads(urllib2.urlopen(req).read())
        else:
            header = {'User-Agent': 'Mozilla/5.0 (Windows NT 6.1; Trident/7.0; rv:11.0) like Gecko',
                      "Content-Type": "application/json"}
            req = urllib2.Request(url=url, data=json.dumps(args),
                                  headers=header)  # urllib.urlencode(args) / json.dumps(args)
            return json.loads(urllib2.urlopen(req).read())

    def formatData(self, time):
        data = datetime.datetime.strptime(time, '%Y-%m-%d %H:%M:%S')
        return data

    def mkdir(self, path):

        isExists = os.path.exists(path)

        if not isExists:
            os.makedirs(path)
            return True
        else:
            return False

    def wokerTypeJudgment(self, timeResult):

        strTimeResult = u""

        if timeResult == "Normal":
            strTimeResult = u"正常"

        if timeResult == "Early":
            strTimeResult = u"早退"

        if timeResult == "Late":
            strTimeResult = u"迟到"

        if timeResult == "SeriousLate":
            strTimeResult = u"严重迟到"

        if timeResult == "Absenteeism":
            strTimeResult = u"旷工迟到"

        if timeResult == "NotSigned":
            strTimeResult = u"未打卡"

        return strTimeResult

    def getAccessToken(self):
        url = 'http://114.215.183.148//queryDAccessTokenForNew.do?company=101'
        res = self.post(url, {})

        if res.has_key("errorCode") and res.get("errorCode") == 0:
            return res['dAccessToken']['accesstoken']
        else:
            return ''

    def getUser(self, sql):

        self.getDBCursor()

        self.cursor.execute(sql)

        _users = self.cursor.fetchall()

        self.closeDBConnect()

        for i in xrange(len(_users)):
            _user = _users[i]

            self.users[_user[0]] = {"name": _user[1]}

    def getAttendance(self, userIds, startTime, daynum, accessToken):

        url = "https://oapi.dingtalk.com/attendance/list?access_token=%s" % accessToken

        args = {
            "userIdList": userIds,  # 员工在企业内的UserID列表，企业用来唯一标识用户的字段。最多不能超过50个
            "offset": 0,  # 表示获取考勤数据的起始点，第一次传0，如果还有多余数据，下次获取传的offset值为之前的offset+limit，0、1、2...依次递增
            "limit": 50  # 表示获取考勤数据的条数，最大不能超过50条
        }

        records = []

        if len(userIds) == 0:
            return

        for i in xrange(daynum):
            _startTime = startTime + datetime.timedelta(days=i)
            _endTime = startTime + datetime.timedelta(days=i + 1) - datetime.timedelta(seconds=1)

            args["workDateFrom"] = str(_startTime)  # 查询考勤打卡记录的起始工作日。格式为“yyyy-MM-dd hh:mm:ss”，hh:mm:ss可以使用00:00:00，具体查询的时候不会起作用，最后将返回此日期从0点到24点的结果
            args["workDateTo"] = str(_endTime)  # 查询考勤打卡记录的结束工作日。格式为“yyyy-MM-dd hh:mm:ss”，hh:mm:ss可以使用00:00:00，具体查询的时候不会起作用，最后将返回此日期从0点到24点的结果。注意，起始与结束工作日最多相隔7天

            res = self.post(url, args, False)

            if res.has_key("errcode") and res.get("errcode") == 0:  # 返回码
                records = records + res.get("recordresult")

        for record in records:

            userId = record.get("userId")
            workdate = time.strftime("%y%m%d", time.localtime(record.get("workDate") / 1000))
            checktype = record.get("checkType")  # 考勤类型 OnDuty ：上班    OffDuty ：下班
            timeresult = record.get("timeResult")  # 时间结果Normal：正常;Early：早退;Late：迟到;SeriousLate：严重迟到；Absenteeism：旷工迟到；NotSigned：未打卡

            if not self.users.has_key(userId):
                user = self.users[userId] = {"name": ""}
            else:
                user = self.users[userId]

            if not user.has_key(workdate):
                check = user[workdate] = ["", ""]
            else:
                check = user.get(workdate)

            if checktype == "OnDuty":
                check[0] = timeresult
            else:
                check[1] = timeresult

            if timeresult != "Normal":

                if self.checkins.has_key(workdate):
                    _checkins = self.checkins.get(workdate)

                    if userId not in _checkins:
                        self.checkins.get(workdate).append(userId)
                else:
                    self.checkins[workdate] = [userId]

    def attendance(self, startTime, daynum, users, accessToken):

        userIds = users.keys()

        for i in xrange(0, len(users.keys()), 25):
            self.getAttendance(userIds[i: i + 25], startTime, daynum, accessToken)

        for date in self.checkins.keys():

            userIds = self.checkins.get(date)

            for i in xrange(0, len(userIds), 10):
                self.checkin(date, userIds[i: i + 10], accessToken, 0)

    def checkin(self, date, userIds, accessToken, cursor):

        _startTime = int(time.mktime(time.strptime("%s040000" % date, "%y%m%d%H%M%S"))) * 1000
        _endTime = int(time.mktime(time.strptime("%s220000" % date, "%y%m%d%H%M%S"))) * 1000

        args = {
            "userid_list": ",".join(userIds),
            "start_time": _startTime,
            "end_time": _endTime,
            "cursor": cursor,
            "size": 100,
            "method": "dingtalk.smartwork.checkin.record.get",
            "session": accessToken,
            "timestamp": time.strftime("%Y-%m-%d %H:%M:%S"),
            "v": "2.0",
            "format": "json",
            "simplify": False
        }

        url = "https://eco.taobao.com/router/rest"

        res = self.post(url, args, True)

        response = res.get("dingtalk_smartwork_checkin_record_get_response")
        result = response.get("result")
        pagelist = result.get("result").get("page_list")

        if pagelist.has_key("checkin_record_vo"):
            records = pagelist.get("checkin_record_vo")
            for i in records:

                userId = i.get("userid")

                if self.users.has_key(userId):
                    user = self.users[userId]
                    checkinTime = datetime.datetime.fromtimestamp(int(i.get("checkin_time") / 1000),pytz.timezone('Asia/Shanghai'))

                    _date = checkinTime.strftime("%y%m%d")
                    if user.has_key(_date):
                        _info = user.get(_date)
                        if checkinTime.strftime("%H%M") < "0930":
                            _info[0] = "Normal"
                        if checkinTime.strftime("%H%M") > "1700":
                            _info[1] = "Normal"

        if result.has_key("next_cursor"):
            cursor = result.get("next_cursor")
            self.checkin(date, userIds, accessToken, cursor)

    def readout(self, ymd):
        workbook = xlwt.Workbook()
        sheet = workbook.add_sheet(str(ymd), cell_overwrite_ok=True)

        sheet.write(0, 0, u"工号")
        sheet.write(0, 1, u"姓名")
        sheet.write(0, 2, u"班次")

        for field in range(3, 34):  # 写表头
            sheet.write(0, field, str(field - 2) + u"号")  # str(field - 2)(1号 --> 31号)

        Y = "20" + ymd[0:2]
        M = ymd[2:4]
        D = ymd[4:6]

        _path = u"/home/fanglin/cronbin/attendance/%s%s" % (Y, M)
        self.mkdir(_path)  # 检测目录是否存在,不存在将创建

        path = u"/home/fanglin/cronbin/attendance/%s%s/%s.xls" % (Y, M, ymd)
        workbook.save(path)

        book = xlrd.open_workbook(path)
        wtbook = xlutils.copy.copy(book)
        sheet = wtbook.get_sheet(0)

        crow = 1
        user = self.users

        for i, v in enumerate(user):
            if user[v].has_key(ymd):
                commute = user[v][ymd]
                shangban = self.wokerTypeJudgment(commute[0])  # 上班 --> 打卡类型(正常,早退,迟到,严重迟到,旷工迟到,未打卡)
                xiaban = self.wokerTypeJudgment(commute[1])  # 下班 --> 打卡类型(正常,早退,迟到,严重迟到,旷工迟到,未打卡)

                if shangban != "" or xiaban != "":

                    if user.has_key(v):
                        userName = user[v]["name"]
                    else:
                        userName = "None"

                    self.delusers[v] = [shangban, xiaban, userName]  # 整理出当前日期下的所有数据

                    nowTimeRows = {"name": userName, "count": 1, "lastTime": ymd}

                    if not self.redisdbconn.exists(ymd):  # 如果当前日期没有考勤数据 说明第一次执行 做未打卡统计
                        if not self.redisdbconn.hexists(self.notSignedKey, v):  # 检测未打卡是否含有这个人
                            if shangban == u"未打卡" and xiaban == u"未打卡":
                                self.redisdbconn.hsetnx(self.notSignedKey, v, nowTimeRows)  # 新增
                        else:
                            datas = self.redisdbconn.hget(self.notSignedKey, v)  # 取值
                            datas = eval(datas)
                            if shangban == u"未打卡" and xiaban == u"未打卡":
                                datas["count"] = int(datas["count"]) + 1
                                self.redisdbconn.hset(self.notSignedKey, v, datas)  # 修改计算
                            elif shangban != u"未打卡" or xiaban != u"未打卡":
                                datas["count"] = 0
                                self.redisdbconn.hset(self.notSignedKey, v, datas)  # 修改计算

                    sheet.write(crow, 0, u'%s' % v)  # 工号
                    sheet.write(crow, 1, u'%s' % userName)  # 名字

                    pattern = xlwt.Pattern()
                    pattern.pattern = xlwt.Pattern.SOLID_PATTERN
                    pattern.pattern_fore_colour = 22
                    if shangban != u"正常":
                        pattern.pattern_fore_colour = 5
                    style = xlwt.XFStyle()
                    style.pattern = pattern
                    sheet.write(crow, int(D) + 2, u'%s' % (shangban), style)

                    patterns = xlwt.Pattern()
                    patterns.pattern = xlwt.Pattern.SOLID_PATTERN
                    patterns.pattern_fore_colour = 22
                    if xiaban != u"正常":
                        patterns.pattern_fore_colour = 5
                    styles = xlwt.XFStyle()
                    styles.pattern = patterns
                    sheet.write(crow + 1, int(D) + 2, u'%s' % (xiaban), styles)

                    sheet.write(crow, 2, u'上班')
                    sheet.write(crow + 1, 2, u'下班')

                    crow = crow + 2
        wtbook.save(path)

    def time(self, startTime, endTime, num=31):
        if type(startTime) != datetime.datetime:
            if startTime.find(u"/") != -1:
                startTime = startTime.replace("/", "-")
                endTime = endTime.replace("/", "-")

            if len(startTime) == 10:
                startTime = "%s 00:00:00" % startTime
                endTime = "%s 23:59:59" % endTime

            startTime = datetime.datetime.strptime(startTime, '%Y-%m-%d %H:%M:%S')
            endTime = datetime.datetime.strptime(endTime, '%Y-%m-%d %H:%M:%S')

        if endTime - startTime <= datetime.timedelta(days=num):
            return True
        else:
            return False

    def main(self, startTime, endTime, db):
        start = datetime.datetime.now()

        self.db = 0 if not 0 <= db <= 15 else self.db = db

        falg = self.time(startTime, endTime)
        if falg == False:
            raise RuntimeError(u'时间范围不在1-31天之内')

        accessToken = self.getAccessToken()

        _startTime = self.formatData("%s 00:00:00" % startTime)
        _endTime = self.formatData("%s 23:59:59" % endTime)

        daynum = (_endTime - _startTime).days + 1

        self.getUser(self.SQL)
        self.redisPool()
        count = 0
        countStartTime = _startTime
        while (count < daynum):
            ymd = str(countStartTime.strftime('%y%m%d'))

            self.attendance(countStartTime, 1, self.users, accessToken)
            countStartTime = countStartTime + datetime.timedelta(days=1)

            self.readout(ymd)  # 操作Redis 当前保留Excel
            self.redisdbconn.hmset(ymd, self.delusers)  # 保存当前日期所有数据

            count = count + 1
        end = datetime.datetime.now()
        print u"执行完毕,总耗时(s): %s" % (end - start).seconds


if __name__ == '__main__':
    """
     1.脚本说明:
        按照指定日期时间范围查询人员考勤和签到记录,整理后导出全部人员每天的考勤数据,每天一个Excel;
        例:导出的Excel路径为/home/fanglin/cronbin/attendance/201806/180625.xml 如果目录不存在将自动创建
        注:获取签到数据时间为, 早04:00:00 -- 晚22:00:00
           签到时间在,早09:30:00之前 -- 晚17:00:00之后 修改考勤数据为正常
     3.例:
        1.python crond_dttendance_collect.py <自动获取昨天时间> db
          python crond_dttendance_collect.py auto 1
          
        2.python crond_dttendance_collect.py <手动输入时间><开始时间><结束时间>
          python crond_dttendance_collect.py noauto 1 2018-06-01 2018-06-05
    """

    config = json.load(open("config.json"))
    dbconf = config.get("database")

    attendance = Attendance(dbconf.get("host"), dbconf.get("username"), dbconf.get("password"), config.get('redis'))

    # action = sys.argv[1]
    # db = sys.argv[2]
    action = "auto"
    db = 2

    if action == "auto":
        nowTime = datetime.datetime.now()
        lists = [1, 2, 3, 4, 5, 6, 7, 8, 9, 10]
        for i, v in enumerate(lists):
            yesterday = nowTime - datetime.timedelta(days=v)
            yesterdayStr = yesterday.strftime("%Y-%m-%d")
            attendance.main(yesterdayStr, yesterdayStr, int(db))
    else:
        attendance.main(sys.argv[3], sys.argv[4])
